package user_answer;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DataBaseOperate {

	public static void main(String[] Arg) throws IOException {

	}

	// 在指定properties文件中以键索值
	public static String readProperty(String key) throws IOException {
		Properties pro = new Properties();
		InputStream ins = DataBaseOperate.class.getClassLoader().getResourceAsStream("jdbc.properties");
		pro.load(ins);
		String str = pro.getProperty(key);
		return str;
	}

	// 连接数据库
	public static Connection linkDB() throws IOException, ClassNotFoundException, SQLException {
		String url = DataBaseOperate.readProperty("url");
		String drive = DataBaseOperate.readProperty("driver");
		String username = DataBaseOperate.readProperty("username");// 数据库名称
		String passwd = DataBaseOperate.readProperty("passwd");
		Class.forName(drive);
		Connection con = DriverManager.getConnection(url, username, passwd);
		return con;
	}

	// 根据题号在question表内搜索题目描述
	public static String selectcontentByQueid(String quesid) throws ClassNotFoundException, IOException, SQLException {
		String sql = "select que from question where queid=?";
		Connection con = DataBaseOperate.linkDB();
		PreparedStatement pre = con.prepareStatement(sql);
		pre.setString(1, quesid);
		ResultSet res = pre.executeQuery();
		String str = null;
		while (res.next()) {
			str = res.getString("que");
		}
		return str;
	}

	// 根据选项号在选项表类查询选项详情
	public static String selectDetailByAnid(String quesid, String anid)
			throws ClassNotFoundException, IOException, SQLException {
		String sql = "select ans from answer where anid=? and queid=?";
		Connection con = DataBaseOperate.linkDB();
		PreparedStatement pre = con.prepareStatement(sql);
		pre.setString(1, anid);
		pre.setString(2, quesid);
		ResultSet res = pre.executeQuery();
		String str = null;
		while (res.next()) {
			str = res.getString("ans");
		}
		return str;
	}

	// 根据用户id在User表中查找username
	public static String selectNameById(String uid) throws ClassNotFoundException, IOException, SQLException {
		String sql = "select username from user where userid=?";
		Connection con = DataBaseOperate.linkDB();
		PreparedStatement pre = con.prepareStatement(sql);
		pre.setString(1, uid);
		ResultSet res = pre.executeQuery();
		String str = null;
		while (res.next()) {
			str = res.getString("username");
		}
		return str;
	}

	// 在答案数据库中查找指定用户的答题情况
	public static List<UserOption> selectAnswerById(String uid)	throws ClassNotFoundException, SQLException, IOException {
		UserOption uo = new UserOption();
		String sql = "select * from userque where uid=? ";
		Connection con = DataBaseOperate.linkDB();
		PreparedStatement pre = con.prepareStatement(sql);// 在答案数据库中执行查询制定用户答案的sql语句
		pre.setString(1, uid);
		ResultSet res = pre.executeQuery();
		List<UserOption> list = new ArrayList<UserOption>();
		String username = DataBaseOperate.selectNameById(uid);
		while (res.next()) {
			String ques = res.getString(1) + ":  " + DataBaseOperate.selectcontentByQueid(res.getString(1));
			String ans = res.getString(2) + ":  "+ DataBaseOperate.selectDetailByAnid(res.getString(1), res.getString(2));
			uo = new UserOption(uid, username, ques, ans);
			list.add(uo);
		}
		con.close();
		return list;
	}

	// 查找所有用户答题情况
	public static List<UserAnswer> selectAnswer() throws ClassNotFoundException, IOException, SQLException {
		Connection con = DataBaseOperate.linkDB();
		String sql = "select * from user";
		PreparedStatement pre = con.prepareStatement(sql);
		ResultSet res = pre.executeQuery();
		UserAnswer ua = new UserAnswer();
		List<UserAnswer> list = new ArrayList<UserAnswer>();
		while (res.next()) {
			String userId = res.getString(1);
			String username = res.getString(2);
			List<UserOption> list1 = DataBaseOperate.selectAnswerById(userId);
			ua = new UserAnswer(userId, username, list1);
			list.add(ua);
		}
		return list;
	}

	// 查找指定用户答题情况
	public static List<UserAnswer> selectAnswer(String uid) throws ClassNotFoundException, IOException, SQLException {
		Connection con = DataBaseOperate.linkDB();
		String sql = "select * from user where userid=?";
		PreparedStatement pre = con.prepareStatement(sql);
		pre.setString(1, uid);
		ResultSet res = pre.executeQuery();
		UserAnswer ua = new UserAnswer();
		List<UserAnswer> list = new ArrayList<UserAnswer>();
		while (res.next()) {
			String userId = res.getString(1);
			String username = res.getString(2);
			List<UserOption> list1 = DataBaseOperate.selectAnswerById(userId);
			ua = new UserAnswer(userId, username, list1);
			list.add(ua);
		}
		return list;

	}
	// 向用户提交答案数据库添加记录
		public static void saveAnswer(String uid, String quesid, String anid)throws ClassNotFoundException, SQLException, IOException {
			String sql = "insert into userque value(?,?,?)";
			Connection con = DataBaseOperate.linkDB();
			PreparedStatement pre = con.prepareStatement(sql);// SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
			pre.setString(1, quesid);// id赋值给数据库第一列
			pre.setString(2, anid);
			pre.setString(3, uid);
			pre.execute();
			con.close();
		}
	
		// 查询数据库
				public static ResultSet selectDB(String sql) throws ClassNotFoundException, SQLException, IOException {
					Connection con = DataBaseOperate.linkDB();
					PreparedStatement pre = con.prepareStatement(sql);
					ResultSet rs = pre.executeQuery();
					return rs;
				}

}
